import MySQLdb as mysql

class WnDbAdapter:
	_db = ""
	_pass = ""
	_db = "seo"
	_user = ""

	def __init__(self, host, user, pswd, db):
		self._host = host
		self._db = db
		self._pass = pswd
		self._user = user

	def getDb(self):
		db = mysql.connect( self._host, self._user, self._pass, self._db )
		db.set_character_set('utf8')
		db.cursor().execute('SET NAMES utf8;')
		db.cursor().execute('SET CHARACTER SET utf8;')
		db.cursor().execute('SET character_set_connection=utf8;')
		return db

	def emptyTables( self ):
		db = self.getDb()
		db.cursor().execute("TRUNCATE TABLE `keywords`")
		db.cursor().execute("TRUNCATE TABLE `search_results`")
		db.commit()
		db.close()

	def removeByKeyword(self, keyword):
		db = self.getDb()
		db.cursor().execute("DELETE FROM `search_results` WHERE `keyword`='%s'" % keyword )
		db.commit()
		db.close()

	def replaceSearchResults( self, keyword, results ):
		self.removeByKeyword(keyword);
		for r in results:
			self.insertSearchResult( keyword, r )

	def insertSearchResult(self, keyword, result):
		db = self.getDb()
		cursor = db.cursor()
		sql = "INSERT INTO `search_results` (keyword, position, url, title, description, html, pr, html_stripped, h1, h2_1, h2_2, links, mobilized) VALUES ('%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') " % \
			  ( mysql.escape_string(keyword), 
			    result['position'], 
			    mysql.escape_string(result['url']), 
			    mysql.escape_string(result['title']), 
			    mysql.escape_string(result['description']), 
			    mysql.escape_string(result['html']),
			    mysql.escape_string(result['pr']),
			    mysql.escape_string(result['html_stripped']),
			    mysql.escape_string(result['h1']),
			    mysql.escape_string(result['h2_1']),
			    mysql.escape_string(result['h2_2']),
			    mysql.escape_string(result['links']),
			    mysql.escape_string(result['mobilized']))
		cursor.execute(sql);
		db.commit()
		db.close()

	def getKeywordResults( self, keyword ):
		db = self.getDb()
		cursor = db.cursor()
		sql = "SELECT `keyword`,`position`, `url`, `html`, `pr`, `html_stripped`, `title`, `description`, `h1`,`h2_1`,`h2_2`,`links`, `mobilized` FROM `search_results` WHERE `keyword` = '%s'" % keyword
		cursor.execute(sql)
		rows = cursor.fetchall()
		
		results = []
		for row in rows:
			results.append(self.formatRowFromDb(row))
		return results
	
	def setColumn(self, keyword, position, column, value):
		sql = "UPDATE `search_results` SET `%s`='%f' WHERE `keyword`='%s' AND `position`=%d " % (column, value, keyword, position)
		db = self.getDb()
		cursor = db.cursor()
		cursor.execute(sql)
		db.commit()
		db.close()

	def formatRowFromDb(self, result):
		return {
			'keyword' : result[0],
			'position' : result[1],
			'link' : result[2],
			'html' : 'html',#result[3],
			'pr' : result[4],
			'html_stripped' : result[5],
			'title' : result[6],
			'description' : result[7],
			'h1' : result[8],
			'h2_1' : result[9],
			'h2_2' : result[10],
			'links' : result[11],
			'mobilized' : result[12],
		}
